<?php
namespace app\admin\controller;

use app\common\controller\AdminBase;
use app\common\model\Gameagent;
use app\common\model\Order;
use app\common\model\Stat as Stats;
use think\facade\Session;

/**
 * 后台首页
 * Class Index
 * @package app\admin\controller
 */
class Stat extends AdminBase
{
    protected function initialize()
    {
        parent::initialize();
    }

    /**
     * 首页
     * @return mixed
     */
    public function recharge()
    {
        $params = $this->request->param();
        $page = $params['page'] ?? 1;

        $stat = new Stats();
        $where = [];
        if(!empty($params['gid'])){
            $where[] = ['gid', '=', $params['gid']];
        }else{
            $where[] = ['gid', '>', '0'];
        }
        if(isset($params['cid']) && $params['cid'] != ""){
            $where[] = ['cid', '=', $params['cid']];
        }
        if (!empty($params['starttime']) && !empty($params['endtime'])) {
            $where[]= ['stat_date','between',[$params['starttime'],$params['endtime']]];
        }elseif (!empty($params['starttime'])) {
            $where[] = ['stat_date','>=',$params['starttime']];
        }elseif (!empty($params['endtime'])) {
            $where[] = ['stat_date','<=',$params['endtime']];
        }else{
            $starttime = date("Y-m-d",strtotime("-7 day"));
            $endtime = date("Y-m-d");
            $where[]= ['stat_date','between',[$starttime,$endtime]];
        }
        if(!isset($params['is_all'])){
            $where[] = ['pay_amount','>',0];
        }
        $list = $stat->getStatList($where, $page,100);

        // 初始化求和变量
        $sumData = [
            'new_users' => 0,
            'new_roles' => 0,
            'active_users' => 0,
            'pay_amount' => 0,
            'pay_users' => 0,
            'pay_times' => 0,
            'arpu' => 0,
            'arppu' => 0
        ];

        if(!empty($list)){
            foreach ($list as $val){
                $sumData['new_users'] += $val['new_users'];
                $sumData['new_roles'] += $val['new_roles'];
                $sumData['active_users'] += $val['active_users'];
                $sumData['pay_amount'] += $val['pay_amount'];
                $sumData['pay_users'] += $val['pay_users'];
                $sumData['pay_times'] += $val['pay_times'];
            }
            $sumData['arpu'] = $sumData['active_users'] > 0 ? round($sumData['pay_amount']/$sumData['active_users'],2) : 0;
            $sumData['arppu'] = $sumData['pay_users'] > 0 ? round($sumData['pay_amount']/$sumData['pay_users'],2) : 0;
        }
        $this->assign("sumData",$sumData);

        $game_list = get_game_list();
        $this->assign("game_list",$game_list);
        // 转换为数组以便返回
        return $this->fetch('index', ['list' => $list]);
    }


    /**
     * 首页 订单统计
     * @return mixed
     */
    public function days()
    {
        $params = $this->request->param();
        $page = $params['page'] ?? 1;

        $stat = new Stats();
        $where = [];
        if(!empty($params['gid'])){
            $where[] = ['gid', '=', $params['gid']];
        }else{
            $where[] = ['gid', '>', '0'];
        }
        if(isset($params['cid']) && $params['cid'] != ""){
            $where[] = ['cid', '=', $params['cid']];
        }
        if (!empty($params['starttime']) && !empty($params['endtime'])) {
            $where[]= ['stat_date','between time',[$params['starttime'],$params['endtime']]];
        }elseif (!empty($params['starttime'])) {
            $where[] = ['stat_date','>=',$params['starttime']];
        }elseif (!empty($params['endtime'])) {
            $where[] = ['stat_date','<=',$params['endtime']];
        }else{
            $starttime = date("Y-m-d",strtotime("-7 day"));
            $endtime = date("Y-m-d");
            $where[]= ['stat_date','between',[$starttime,$endtime]];
        }


        $statData = $stat->field("sum(new_users) as new_users,sum(new_roles) as new_roles,sum(active_users) as active_users,
            sum(pay_amount) as pay_amount,sum(pay_users) as pay_users,sum(pay_times) as pay_times,
            ROUND(SUM(pay_amount)/NULLIF(SUM(active_users), 0), 2) AS arpu,
            ROUND(SUM(pay_amount)/NULLIF(sum(pay_users), 0), 2) AS arppu")
            ->where($where)
            ->paginate(100)->toArray();


        // 初始化求和变量
        $sumData = [
            'new_users' => 0,
            'new_roles' => 0,
            'active_users' => 0,
            'pay_amount' => 0,
            'pay_users' => 0,
            'pay_times' => 0,
            'arpu' => 0,
            'arppu' => 0
        ];

        if(!empty($statData['data'][0])){
            $sumData = $statData['data'][0];
        }

        $list = $stat->getStatDayList($where, $page,100);

//        echo $stat->getLastSql();exit;

        $this->assign("sumData",$sumData);

        $game_list = get_game_list();
        $this->assign("game_list",$game_list);
        $this->assign("action","index");
        // 转换为数组以便返回
        return $this->fetch('days', ['list' => $list]);
    }


    /**
     * 数据统计 月份
     */
    public function months()
    {
        $params = $this->request->param();
        $page = $params['page'] ?? 1;

        $stat = new Stats();
        $where = [];
        if(!empty($params['gid'])){
            $where[] = ['gid', '=', $params['gid']];
        }else{
            $where[] = ['gid', '>', '0'];
        }
        if(isset($params['cid']) && $params['cid'] != ""){
            $where[] = ['cid', '=', $params['cid']];
        }
        if (!empty($params['starttime']) && !empty($params['endtime'])) {
            $where[]= ['stat_date','between time',[$params['starttime'],$params['endtime']]];
        }elseif (!empty($params['starttime'])) {
            $where[] = ['stat_date','>=',$params['starttime']];
        }elseif (!empty($params['endtime'])) {
            $where[] = ['stat_date','<=',$params['endtime']];
        }else{
            // 默认当前月份
            $starttime = date("Y-m")."-01";
            $endtime = date("Y-m-d");
//            $endtime = date("Y-m")."-".date("t");
            $where[]= ['stat_date','between',[$starttime,$endtime]];
        }

        $list = $stat->field("DATE_FORMAT(stat_date, '%Y-%m') as month_date,sum(new_users) as new_users,sum(new_roles) as new_roles,
            sum(active_users) as active_users,sum(pay_amount) as pay_amount,sum(pay_users) as pay_users,sum(pay_times) as pay_times,
            ROUND( SUM( pay_amount ) / NULLIF( SUM( new_users ), 0 ), 2 ) AS arpu,
            ROUND( SUM( pay_amount ) / NULLIF( sum( pay_users ), 0 ), 2 ) AS arppu ")
            ->where($where)
            ->group("DATE_FORMAT(stat_date, '%Y-%m')")
            ->order("month_date desc")
            ->paginate(100);

        // 初始化求和变量
        $sumData = [
            'new_users' => 0,
            'new_roles' => 0,
            'active_users' => 0,
            'pay_amount' => 0,
            'pay_users' => 0,
            'pay_times' => 0,
            'arpu' => 0,
            'arppu' => 0,
        ];

        if(!empty($list)){
            foreach ($list as $val){
                $sumData['new_users'] += $val['new_users'];
                $sumData['new_roles'] += $val['new_roles'];
                $sumData['active_users'] += $val['active_users'];
                $sumData['pay_amount'] += $val['pay_amount'];
                $sumData['pay_users'] += $val['pay_users'];
                $sumData['pay_times'] += $val['pay_times'];
            }
            $sumData['arpu'] = $sumData['active_users'] > 0 ? round($sumData['pay_amount']/$sumData['active_users'],2) : 0;
            $sumData['arppu'] = $sumData['pay_users'] > 0 ? round($sumData['pay_amount']/$sumData['pay_users'],2) : 0;
        }

        $this->assign("sumData",$sumData);

        $game_list = get_game_list();
        $this->assign("game_list",$game_list);
        $this->assign("action","months");
        // 转换为数组以便返回
        return $this->fetch('months', ['list' => $list]);
    }
    /**
     * 首页 订单统计
     * @return mixed
     */
    public function games()
    {
        $params = $this->request->param();
        $page = $params['page'] ?? 1;

        $stat = new Stats();
        $where = [];
        if(!empty($params['gid'])){
            $where[] = ['gid', '=', $params['gid']];
        }else{
            $params['gid'] = "";
        }
        if(isset($params['cid']) && $params['cid'] != ""){
            $where[] = ['cid', '=', $params['cid']];
        }else{
            $params['cid'] = "";
        }
        if (!empty($params['starttime']) && !empty($params['endtime'])) {
            $where[]= ['stat_date','between time',[$params['starttime'],$params['endtime']]];
        }elseif (!empty($params['starttime'])) {
            $where[] = ['stat_date','>=',$params['starttime']];
        }elseif (!empty($params['endtime'])) {
            $where[] = ['stat_date','<=',$params['endtime']];
        }
        $list = $stat->getStatDayList($where, $page,100);
        foreach ($list as &$val){

            $activeUsers = $val['active_users'] ?? 0;
            $payAmount = $val['pay_amount'] ?? 0;
            $payUsers = $val['pay_users'] ?? 0;

            $arpu = $activeUsers > 0 ? round($payAmount / $activeUsers, 2) : 0;
            $arppu = $payUsers > 0 ? round($payAmount / $payUsers, 2) : 0;

            $val['arpu'] = $arpu;
            $val['arppu'] = $arppu;
        }
        $game_list = get_game_list();
        $this->assign("game_list",$game_list);
        $this->assign("params",$params );
        $this->assign("action","index");
        // 转换为数组以便返回
        return $this->fetch('days', ['list' => $list]);
    }


    public function gaode()
    {
        $params = $this->request->param();
        $page = $params['page'] ?? 1;

        $stat = new Stats();
        $where = [];
        $where[] = ['cid', '=', 5];
        if(!empty($params['gid'])){
            $where[] = ['gid', '=', $params['gid']];
        }else{
            $where[] = ['gid', '>', '0'];
        }
        if (!empty($params['starttime']) && !empty($params['endtime'])) {
            $where[]= ['stat_date','between',[$params['starttime'],$params['endtime']]];
        }elseif (!empty($params['starttime'])) {
            $where[] = ['stat_date','>=',$params['starttime']];
        }elseif (!empty($params['endtime'])) {
            $where[] = ['stat_date','<=',$params['endtime']];
        }else{
            $starttime = date("Y-m-d",strtotime("-7 day"));
            $endtime = date("Y-m-d");
            $where[]= ['stat_date','between',[$starttime,$endtime]];
        }
        if(!isset($params['is_all'])){
            $where[] = ['pay_amount','>',0];
        }

        $list = $stat->getStatList($where, $page,100);

        // 初始化求和变量
        $sumData = [
            'new_users' => 0,
            'new_roles' => 0,
            'active_users' => 0,
            'pay_amount' => 0,
            'pay_users' => 0,
            'pay_times' => 0,
            'arpu' => 0,
            'arppu' => 0
        ];

        if(!empty($list)){
            foreach ($list as $val){
                $sumData['new_users'] += $val['new_users'];
                $sumData['new_roles'] += $val['new_roles'];
                $sumData['active_users'] += $val['active_users'];
                $sumData['pay_amount'] += $val['pay_amount'];
                $sumData['pay_users'] += $val['pay_users'];
                $sumData['pay_times'] += $val['pay_times'];
            }
            $sumData['arpu'] = $sumData['active_users'] > 0 ? round($sumData['pay_amount']/$sumData['active_users'],2) : 0;
            $sumData['arppu'] = $sumData['pay_users'] > 0 ? round($sumData['pay_amount']/$sumData['pay_users'],2) : 0;
        }
        $this->assign("sumData",$sumData);

        $game_list = get_game_list();
        $this->assign("game_list",$game_list);
        // 转换为数组以便返回
        return $this->fetch('gaode', ['list' => $list]);
    }

    public function gaode_days()
    {
        $params = $this->request->param();
        $page = $params['page'] ?? 1;

        $stat = new Stats();
        $where = [];
        if(!empty($params['gid'])){
            $where[] = ['gid', '=', $params['gid']];
        }else{
            $where[] = ['gid', '>', '0'];
        }
        $where[] = ['cid', '=', 5];
        if (!empty($params['starttime']) && !empty($params['endtime'])) {
            $where[]= ['stat_date','between time',[$params['starttime'],$params['endtime']]];
        }elseif (!empty($params['starttime'])) {
            $where[] = ['stat_date','>=',$params['starttime']];
        }elseif (!empty($params['endtime'])) {
            $where[] = ['stat_date','<=',$params['endtime']];
        }else{
            $starttime = date("Y-m-d",strtotime("-7 day"));
            $endtime = date("Y-m-d");
            $where[]= ['stat_date','between',[$starttime,$endtime]];
        }


        $statData = $stat->field("sum(new_users) as new_users,sum(new_roles) as new_roles,sum(active_users) as active_users,
            sum(pay_amount) as pay_amount,sum(pay_users) as pay_users,sum(pay_times) as pay_times,
            ROUND(SUM(pay_amount)/NULLIF(SUM(active_users), 0), 2) AS arpu,
            ROUND(SUM(pay_amount)/NULLIF(sum(pay_users), 0), 2) AS arppu")
            ->where($where)
            ->paginate(100)->toArray();


        // 初始化求和变量
        $sumData = [
            'new_users' => 0,
            'new_roles' => 0,
            'active_users' => 0,
            'pay_amount' => 0,
            'pay_users' => 0,
            'pay_times' => 0,
            'arpu' => 0,
            'arppu' => 0
        ];

        if(!empty($statData['data'][0])){
            $sumData = $statData['data'][0];
        }

        $list = $stat->getStatDayList($where, $page,100);

//        echo $stat->getLastSql();exit;

        $this->assign("sumData",$sumData);

        $game_list = get_game_list();
        $this->assign("game_list",$game_list);
        $this->assign("action","index");
        // 转换为数组以便返回
        return $this->fetch('gaode_days', ['list' => $list]);
    }

    public function gaode_months()
    {
        $params = $this->request->param();
        $page = $params['page'] ?? 1;

        $stat = new Stats();
        $where = [];
        if(!empty($params['gid'])){
            $where[] = ['gid', '=', $params['gid']];
        }else{
            $where[] = ['gid', '>', '0'];
        }
        if(isset($params['cid']) && $params['cid'] != ""){
            $where[] = ['cid', '=', $params['cid']];
        }
        if (!empty($params['starttime']) && !empty($params['endtime'])) {
            $where[]= ['stat_date','between time',[$params['starttime'],$params['endtime']]];
        }elseif (!empty($params['starttime'])) {
            $where[] = ['stat_date','>=',$params['starttime']];
        }elseif (!empty($params['endtime'])) {
            $where[] = ['stat_date','<=',$params['endtime']];
        }else{
            // 默认当前月份
            $starttime = date("Y-m")."-01";
            $endtime = date("Y-m-d");
//            $endtime = date("Y-m")."-".date("t");
            $where[]= ['stat_date','between',[$starttime,$endtime]];
        }

        $list = $stat->field("DATE_FORMAT(stat_date, '%Y-%m') as month_date,sum(new_users) as new_users,sum(new_roles) as new_roles,
            sum(active_users) as active_users,sum(pay_amount) as pay_amount,sum(pay_users) as pay_users,sum(pay_times) as pay_times,
            ROUND( SUM( pay_amount ) / NULLIF( SUM( new_users ), 0 ), 2 ) AS arpu,
            ROUND( SUM( pay_amount ) / NULLIF( sum( pay_users ), 0 ), 2 ) AS arppu ")
            ->where($where)
            ->group("DATE_FORMAT(stat_date, '%Y-%m')")
            ->order("month_date desc")
            ->paginate(100);

        // 初始化求和变量
        $sumData = [
            'new_users' => 0,
            'new_roles' => 0,
            'active_users' => 0,
            'pay_amount' => 0,
            'pay_users' => 0,
            'pay_times' => 0,
            'arpu' => 0,
            'arppu' => 0,
        ];

        if(!empty($list)){
            foreach ($list as $val){
                $sumData['new_users'] += $val['new_users'];
                $sumData['new_roles'] += $val['new_roles'];
                $sumData['active_users'] += $val['active_users'];
                $sumData['pay_amount'] += $val['pay_amount'];
                $sumData['pay_users'] += $val['pay_users'];
                $sumData['pay_times'] += $val['pay_times'];
            }
            $sumData['arpu'] = $sumData['active_users'] > 0 ? round($sumData['pay_amount']/$sumData['active_users'],2) : 0;
            $sumData['arppu'] = $sumData['pay_users'] > 0 ? round($sumData['pay_amount']/$sumData['pay_users'],2) : 0;
        }

        $this->assign("sumData",$sumData);

        $game_list = get_game_list();
        $this->assign("game_list",$game_list);
        $this->assign("action","months");
        // 转换为数组以便返回
        return $this->fetch('gaode_months', ['list' => $list]);
    }

    public function game_stat_day()
    {
        $params = $this->request->param();
        $page = $params['page'] ?? 1;

        $stat = new Stats();
        $where = [];
        if(!empty($params['gid'])){
            $where[] = ['gid', '=', $params['gid']];
        }else{
            $where[] = ['gid', '>', '0'];
        }
        $where[] = ['cid', '>', 5];
        if(isset($params['cid']) && $params['cid'] != ""){
            $where[] = ['cid', '=', $params['cid']];
        }
        if (!empty($params['starttime']) && !empty($params['endtime'])) {
            $where[]= ['stat_date','between time',[$params['starttime'],$params['endtime']]];
        }elseif (!empty($params['starttime'])) {
            $where[] = ['stat_date','>=',$params['starttime']];
        }elseif (!empty($params['endtime'])) {
            $where[] = ['stat_date','<=',$params['endtime']];
        }else{
            $starttime = date("Y-m-d",strtotime("-7 day"));
            $endtime = date("Y-m-d");
            $where[]= ['stat_date','between',[$starttime,$endtime]];
        }


        // 数据汇总
        $statData = $stat->field("sum(new_users) as new_users,sum(new_roles) as new_roles,sum(active_users) as active_users,
            sum(pay_amount) as pay_amount,sum(pay_users) as pay_users,sum(pay_times) as pay_times,
            ROUND(SUM(pay_amount)/NULLIF(SUM(active_users), 0), 2) AS arpu,
            ROUND(SUM(pay_amount)/NULLIF(sum(pay_users), 0), 2) AS arppu")
            ->where($where)
            ->paginate(100)->toArray();


        // 初始化求和变量
        $sumData = [
            'new_users' => 0,
            'new_roles' => 0,
            'active_users' => 0,
            'pay_amount' => 0,
            'pay_users' => 0,
            'pay_times' => 0,
            'arpu' => 0,
            'arppu' => 0
        ];

        if(!empty($statData['data'][0])){
            $sumData = $statData['data'][0];
        }


        $list = $stat->field('cid,stat_date, sum(new_users) as new_users, sum(new_roles) as new_roles,
            sum(active_users) as active_users, sum(pay_amount) as pay_amount, sum(pay_users) as pay_users, sum(pay_times) as pay_times,
            ROUND(SUM(pay_amount)/NULLIF(SUM(active_users), 0), 2) AS arpu,
            ROUND(SUM(pay_amount)/NULLIF(sum(pay_users), 0), 2) AS arppu')
            ->where($where)
            ->order('stat_date desc')
            ->group('stat_date,cid')
            ->paginate(100, false, ['page' => $page,'query' =>request()->param()]);


        $this->assign("sumData",$sumData);

        $game_list = getGameAgentList();
        $this->assign("game_list",$game_list);
        $this->assign("action","index");
        // 转换为数组以便返回
        return $this->fetch('game_stat_day', ['list' => $list]);
    }

    public function game_stat_months()
    {
        $params = $this->request->param();
        $page = $params['page'] ?? 1;

        $stat = new Stats();
        $where = [];
        if(!empty($params['gid'])){
            $where[] = ['gid', '=', $params['gid']];
        }else{
            $where[] = ['gid', '>', '0'];
        }

        $where[] = ['cid', '>', '5'];
        if(isset($params['cid']) && $params['cid'] != ""){
            $where[] = ['cid', '=', $params['cid']];
        }
        if (!empty($params['starttime']) && !empty($params['endtime'])) {
            $where[]= ['stat_date','between time',[$params['starttime'],$params['endtime']]];
        }elseif (!empty($params['starttime'])) {
            $where[] = ['stat_date','>=',$params['starttime']];
        }elseif (!empty($params['endtime'])) {
            $where[] = ['stat_date','<=',$params['endtime']];
        }else{
            // 默认当前月份
            $starttime = date("Y-m")."-01";
            $endtime = date("Y-m-d");
//            $endtime = date("Y-m")."-".date("t");
            $where[]= ['stat_date','between',[$starttime,$endtime]];
        }

        $list = $stat->field("cid,DATE_FORMAT(stat_date, '%Y-%m') as month_date,sum(new_users) as new_users,sum(new_roles) as new_roles,
            sum(active_users) as active_users,sum(pay_amount) as pay_amount,sum(pay_users) as pay_users,sum(pay_times) as pay_times,
            ROUND( SUM( pay_amount ) / NULLIF( SUM( new_users ), 0 ), 2 ) AS arpu,
            ROUND( SUM( pay_amount ) / NULLIF( sum( pay_users ), 0 ), 2 ) AS arppu ")
            ->where($where)
            ->group("DATE_FORMAT(stat_date, '%Y-%m'),cid")
            ->order("month_date desc")
            ->paginate(100);


        // 初始化求和变量
        $sumData = [
            'new_users' => 0,
            'new_roles' => 0,
            'active_users' => 0,
            'pay_amount' => 0,
            'pay_users' => 0,
            'pay_times' => 0,
            'arpu' => 0,
            'arppu' => 0,
        ];

        if(!empty($list)){
            foreach ($list as $val){
                $sumData['new_users'] += $val['new_users'];
                $sumData['new_roles'] += $val['new_roles'];
                $sumData['active_users'] += $val['active_users'];
                $sumData['pay_amount'] += $val['pay_amount'];
                $sumData['pay_users'] += $val['pay_users'];
                $sumData['pay_times'] += $val['pay_times'];
            }
            $sumData['arpu'] = $sumData['active_users'] > 0 ? round($sumData['pay_amount']/$sumData['active_users'],2) : 0;
            $sumData['arppu'] = $sumData['pay_users'] > 0 ? round($sumData['pay_amount']/$sumData['pay_users'],2) : 0;
        }

        $this->assign("sumData",$sumData);

        $game_list = getGameAgentList();
        $this->assign("game_list",$game_list);
        $this->assign("action","months");
        // 转换为数组以便返回
        return $this->fetch('game_stat_months', ['list' => $list]);
    }
}